package com.dbutil;

import com.entity.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UtilJdbc {

    private static final String driver = "com.mysql.cj.jdbc.Driver";
    private static final String url="jdbc:mysql://localhost/kgc";
    private static final String username="root";
    private static final String password="root";

    /**
     * 注册用户
     * @return
     */
    public boolean regit(String uName,String uPassword){

        String str="INSERT into `user`(user_name,user_password) VALUES('"+uName+"','"+uPassword+"');";
        boolean bo=false;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            bo=stmt.execute(str); //发送执行sql语句
            bo=true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return bo;
    }
    /**
     * 注册user_role中间表信息
     * @return
     */
    public boolean doUserRole(Integer uid,Integer rid){

        String str="INSERT into user_role(user_id,role_id) VALUES("+uid+","+rid+");";
        boolean to=false;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            to=stmt.execute(str); //发送执行sql语句
            to=true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return to;
    }

    /**
     * 查询所有作业
     * @param
     * @return
     */
    public List<Push_work> workList(){
        String str="select *from push_work;";
        ResultSet rs=null;
        List<Push_work> list = new ArrayList<>();
        try{
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            rs = stmt.executeQuery(str);
            while (rs.next()){
                int id = rs.getInt("id");
                String workName = rs.getString("workName");
                Date date = rs.getDate("pushDate");
                String work = rs.getString("work");
                Push_work push_work = new Push_work(id,workName,date,work);
                list.add(push_work);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 登录

     * @return
     */
    public User login(String uName,String uPassword){

        String str="select *from user where user_name='"+uName+"' and user_password='"+uPassword+"'";
        ResultSet set=null;
        User user=null;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            set=stmt.executeQuery(str); //发送执行sql语句
            if(set.next()){
                int id = set.getInt("id");
                user=new User(id,uName,uPassword);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return user;
    }


    /**
     * 登录

     * @return
     */
    public Role findRole(String type){

        String str="select *from role where role_name='"+type+"' ";
        ResultSet set=null;
        Role role=null;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            set=stmt.executeQuery(str); //发送执行sql语句
            if(set.next()){
                int id = set.getInt("id");
                role=new Role(id,type);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return role;
    }

    public Push_work findByidWork(Integer id){

        String str="select *from push_work where id='"+id+"' ";
        ResultSet set=null;
        Push_work works=null;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            set=stmt.executeQuery(str); //发送执行sql语句
            if(set.next()){
                String workName = set.getString("workName");
                Date date = set.getDate("pushDate");
                String work = set.getString("work");
                works = new Push_work(id,workName,date,work);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return works;
    }

    /**
     * 插入作业
     * @param wid
     * @param wname
     * @param wdate
     * @param answer
     * @return
     */
    public boolean inserWorks(int wid,String wname,String wdate,String answer) {
        String str="insert into works(uid,name,pushDate,doWork)value ("+wid+",'"+wname+"','"+wdate+"','"+answer+"' )";
        boolean flag=false;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            flag=stmt.execute(str); //发送执行sql语句
            flag=true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return flag;
    }

    public Works findWorkByNameDate(String name, String date) {
        String str="select *from works where name='"+name+"' and pushDate='"+date+"'";
        ResultSet set=null;
        Works works=null;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            set=stmt.executeQuery(str); //发送执行sql语句
            if(set.next()){
                int id = set.getInt("id");
                int uid = set.getInt("uid");
                String doWork = set.getString("doWork");
                Date pushDate = set.getDate("pushDate");
                works = new Works(id,uid,name,pushDate,doWork);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return works;

    }

    public User_role findUserrole(Integer uid) {
        String str="select *from user_role where user_id="+uid+" ";
        ResultSet set=null;
        User_role ur=null;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            set=stmt.executeQuery(str); //发送执行sql语句
            if(set.next()){
                int id = set.getInt("id");
                int role_id = set.getInt("role_id");
                ur = new User_role(id,uid,role_id);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return ur;
    }

    public List<Works> doWork() {
        String str="SELECT works.*,push_work.workName from works,push_work where works.pushDate=push_work.pushDate";
        ResultSet rs=null;
        List<Works> list = new ArrayList<>();
        try{
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            rs = stmt.executeQuery(str);
            while (rs.next()){
                int id = rs.getInt("id");
                int uid = rs.getInt("uid");
                String name = rs.getString("name");
                Date pushDate = rs.getDate("pushDate");
                String doWork = rs.getString("doWork");
                String workName = rs.getString("workName");
                Works works = new Works(id,uid,name,pushDate,doWork,workName);
                list.add(works);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    public boolean insertPushWork(String toworkName,String topushDate,String towork) {
        String str="insert into push_work(workName,pushDate,work) values ('"+toworkName+"','"+topushDate+"','"+towork+"')";
        boolean flag=false;
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            flag=stmt.execute(str); //发送执行sql语句
            flag=true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }         return flag;
    }
    public List<Push_work> PushedWork(){
        String sql="select*from push_work";
        ResultSet set=null;
        List<Push_work> list=new ArrayList<>();
        try {
            Class.forName(driver);//注册连接数据库对象//java程序连接数据库的驱动包注册
            Connection conn= DriverManager.getConnection(url,username,password);//数据库信息
            Statement stmt =conn.createStatement();    //创建 执行sql语句对象
            set= stmt.executeQuery(sql); //发送执行sql语句
//            if(set.next()){
//                int id = set.getInt("id");
//                role=new Role(id,type);
//            }
            while (set.next()){
                int id = set.getInt("id");
                String workName = set.getString("workName");
                Date pushDate = set.getDate("pushDate");
                String work = set.getString("work");
                Push_work pw = new Push_work(id,workName,pushDate,work);
                list.add(pw);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
}
